import re
from urllib import request
from MysqlUtils import *
import pymysql

'''
批量插入mysql
'''
class SpiderTest(object):
    # 数据库链接实例化
    global conn, cursor  # 全局变量 也可以用self.conn调用
    mysql = MysqlUtils()
    conn = mysql.get_mysql_connection()
    # 创建游标
    cursor = conn.cursor()

    url = 'https://coding.imooc.com/'
    url2 = 'https://coding.imooc.com/?sort=0&unlearn=0&page=2'
    div_pattern = '<div class="shizhan-intro-box">([\s\S]*?)</div>'
    courses_name_pattern = '<p class="shizan-name" title=([\s\S]*?)</p>'
    name_pattern = 'class="">([\s\S]*?)</a>\\n'

    # 抓取页面
    def __fetch_content(self):
        opUrl = request.urlopen(SpiderTest.url)
        htmls = opUrl.read()
        htmls = str(htmls, encoding='utf-8')
        # print(htmls)
        return htmls

    # 正则分析
    def __analysis(self, htmls):
        root_html = re.findall(SpiderTest.div_pattern, htmls)
        courses_namelist = re.findall(SpiderTest.courses_name_pattern, str(root_html))
        # 组装数据
        names = self.__data_assembly(courses_namelist)
        return names

    '''
    组装数据格式
    '''
    def __data_assembly(self, nameList):

        names = []
        # print(nameList)
        for i in range(len(nameList)):
            oname = nameList[i]
            start_index=oname.index('>')
            name = oname[start_index+1:]
            names.append(name)
        #print(names)
        return names
    '''
    启动入口
    '''

    def go(self):
        root_html = self.__fetch_content()
        nameList = self.__analysis(root_html)

        1 == 1
        print(nameList, end='')
        values = nameList
        valueStrings = []
        # # print(nameList)
        # for i in range(len(nameList)):
        #     #  onename = nameList[i]['name']
        #     course_name = nameList[i]['name']
        #     values.append(course_name)
        #
        # print('名称：', course_name)

        try:
           # self.cursor.execute('insert into spidertest(name) values(%s)', nameList)
            for key in range(len(values)):
                valueStrings.append(values[key])
                # 提交
            cursor.executemany('insert into spidertest(name) values(%s)', valueStrings)
            conn.commit()
        except:
            conn.rollback()
            print('插入数据失败：')
        #  print(values)
        print('valueStrings:',valueStrings)
        # 关闭
        cursor.close()
        conn.close()


spiderTest = SpiderTest()

spiderTest.go()
